Date type strange behavior [message #24023] |
Tue, 21 January 2003 10:26 |
Igor Starodubtsev
Messages: 3 Registered: January 2003
|
Junior Member |
|
|
Setup:
SQL> describe is_date_issue;
Name Null? Type
------------- -------- ----
MONTH_DT NOT NULL DATE
CYCLE_ID NOT NULL NUMBER
This is a big partitioned table with all correct data
except just one cycle_id, say cycle_id = 13.
Any query with to_date conversion like:
SQL> select count(*) from is_date_issue
2 where cycle_id = 13
3 and month_dt =
4 to_date('01-JAN-2003','DD-MON-YYYY');
will return
COUNT(*)
--------
0
Only something like the following
SQL> select count(*) from is_date_issue
2 where cycle_id = 13
3 and trunc(month_dt) =
4 to_date('01-JAN-2003','DD-MON-YYYY');
will return data:
COUNT(*)
--------
123
The conversion of month_dt to char will also work, but it's not the point how to make it work.
The question is why Oracle doesn't recognize month_dt the regular way, as for any other cycle_id?
|
|
|
Re: Date type strange behavior [message #24025 is a reply to message #24023] |
Tue, 21 January 2003 10:49 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Because month_dt must have a time component to it. When you're sayingSQL> select count(*) from is_date_issue
2 where cycle_id = 13
3 and month_dt =
4 to_date('01-JAN-2003','DD-MON-YYYY'); you are looking for all rows whose month_dt is equal to midnight on the morning of January 1st, 2003. If all month_dts have a time component to them, even if their date components are all 01-JAN-2003, then the condition of equality will not be met and you'll get no rows returned.
What TRUNC is doing for you is zeroing any DATE's time component back to midnight, hence you're getting your matches with TRUNC.
Try running this query; it should list the hours of the day (members of the set 00, 01, 02, .., 22, 23) and the quanities of is_date_issue rows whose time components fall within those hours of the day.SELECT RPAD(TO_CHAR(idi.month_dt,'HH24'),4) hour
, COUNT(TO_CHAR(idi.month_dt,'HH24')) qty
FROM is_date_issue idi
WHERE idi.month_dt IS NOT NULL
AND idi.cycle_id = 13
GROUP BY RPAD(TO_CHAR(idi.month_dt,'HH24'),4)
If they're all midnight, you'll get one row for the hour "00"; if not, the rows will be otherwise dispersed throughout the day.
Hope this helps,
A
|
|
|
Re: Date type strange behavior [message #24030 is a reply to message #24023] |
Tue, 21 January 2003 11:36 |
Igor Starodubtsev
Messages: 3 Registered: January 2003
|
Junior Member |
|
|
All rows for that cycle are set to
'01-JAN-2003 00:00:00' as for any other cycle.
Therefore, trunc() shouldn't make any difference.
It appeares could be some other garbage in that field
and as a precausion it's better to truncate it anyway.
Thank you for your help.
|
|
|
Re: Date type strange behavior [message #24032 is a reply to message #24023] |
Tue, 21 January 2003 11:57 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
quote:
----------------------------------------------------------------------
All rows for that cycle are set to
'01-JAN-2003 00:00:00' as for any other cycle.
Therefore, trunc() shouldn't make any difference.
It appeares could be some other garbage in that field
and as a precausion it's better to truncate it anyway.
Thank you for your help.
----------------------------------------------------------------------
That doesn't make any sense.
First of all, what do you get when you do
SELECT COUNT(*)
FROM is_date_issue idi
WHERE idi.cycle_id = 13
AND idi.month_dt BETWEEN TO_DATE('20030101'
, 'YYYYMMDD')
AND TO_DATE('20030101235959'
, 'YYYYMMDDHH24MISS') ? And secondly, what do you get when you do
SELECT COUNT(*)
FROM is_date_issue idi
WHERE idi.cycle_id = 13
AND idi.month_dt BETWEEN TO_DATE('20030101000001'
, 'YYYYMMDDHH24MISS')
AND TO_DATE('20030101235959'
, 'YYYYMMDDHH24MISS') ?
Art
|
|
|